﻿Imports MySql.Data.MySqlClient

Public Class Data

#Region "ExecuteDataset"
    ''' <summary>
    ''' Crea y abre la conexion a la bd, ejecuta un procedimiento almacenado y retorna un dataset con sus resultados
    ''' </summary>
    ''' <param name="cadenaConexion"></param>
    ''' <param name="nombreProcedimiento"></param>
    ''' <param name="commandParameters"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecuteDataSet(ByVal cadenaConexion As String, ByVal nombreProcedimiento As String, ByVal ParamArray commandParameters() As MySqlParameter) As DataSet
        If (cadenaConexion Is Nothing OrElse cadenaConexion.Length = 0) Then Throw New Exception("La cadena de conexión no puede ser null")

        ' Create & open a SqlConnection, and dispose of it after we are done
        Dim connection As MySqlConnection
        Try
            connection = New MySqlConnection(cadenaConexion)
            connection.Open()

            ' Call the overload that takes a connection in place of the connection string
            Return ExecuteDataSet(connection, nombreProcedimiento, commandParameters)
        Finally
            If Not connection Is Nothing Then connection.Dispose()
        End Try
    End Function

    ''' <summary>
    ''' Recibe una conexion ya abierta a la base de datos, ejecuta el sp que le llega y retorna sus resultados
    ''' </summary>
    ''' <param name="connection"></param>
    ''' <param name="nombreProcedimiento"></param>
    ''' <param name="commandParameters"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Shared Function ExecuteDataset(ByVal connection As MySqlConnection, ByVal nombreProcedimiento As String, ByVal ParamArray commandParameters() As MySqlParameter) As DataSet
        ' Create a command and prepare it for execution
        Dim cmd As New MySqlCommand
        Dim ds As New DataSet
        Dim dataAdatpter As MySqlDataAdapter
        Dim mustCloseConnection As Boolean = False

        'Agrega los parámetros (si llegan)
        If Not (commandParameters Is Nothing) Then
            AttachParameters(cmd, commandParameters)
        End If

        Try
            ' Create the DataAdapter & DataSet
            cmd.Connection = connection
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = nombreProcedimiento
            dataAdatpter = New MySqlDataAdapter(cmd)

            ' Fill the DataSet using default values for DataTable names, etc
            dataAdatpter.Fill(ds)

            ' Detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear()
        Finally
            If (Not dataAdatpter Is Nothing) Then dataAdatpter.Dispose()
        End Try
        If (mustCloseConnection) Then connection.Close()

        ' Return the dataset
        Return ds

    End Function

#End Region

#Region "ExecuteNonQuery"

    Public Shared Function ExecuteNonQuery(ByVal cadenaConexion As String, ByVal nombreProcedimiento As String, ByVal ParamArray commandParameters() As MySqlParameter) As Integer
        If (cadenaConexion Is Nothing OrElse cadenaConexion.Length = 0) Then Throw New Exception("La cadena de conexión no puede ser null")
        ' Create & open a SqlConnection, and dispose of it after we are done
        Dim connection As MySqlConnection
        Try
            connection = New MySqlConnection(cadenaConexion)
            connection.Open()

            ' Call the overload that takes a connection in place of the connection string
            Return ExecuteNonQuery(connection, nombreProcedimiento, commandParameters)
        Finally
            If Not connection Is Nothing Then connection.Dispose()
        End Try
    End Function ' ExecuteNonQuery

    Private Shared Function ExecuteNonQuery(ByVal connection As MySqlConnection, ByVal nombreProcedimiento As String, ByVal ParamArray commandParameters() As MySqlParameter) As Integer
        If (connection Is Nothing) Then Throw New ArgumentNullException("connection")

        ' Create a command and prepare it for execution
        Dim cmd As New MySqlCommand
        Dim retval As Integer
        Dim mustCloseConnection As Boolean = False

        'Agrega los parámetros (si llegan)
        If Not (commandParameters Is Nothing) Then
            AttachParameters(cmd, commandParameters)
        End If

        ' Finally, execute the command
        cmd.Connection = connection
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = nombreProcedimiento
        retval = cmd.ExecuteNonQuery()        

        ' Detach the SqlParameters from the command object, so they can be used again
        cmd.Parameters.Clear()

        If (mustCloseConnection) Then connection.Close()

        Return retval

    End Function
#End Region

#Region "ExecuteSelect"


    Public Shared Function ExecuteSelect(ByVal cadenaConexion As String, ByVal consulta As String, ByVal ParamArray commandParameters() As MySqlParameter) As DataSet
        If (cadenaConexion Is Nothing OrElse cadenaConexion.Length = 0) Then Throw New Exception("La cadena de conexión no puede ser null")
        ' Create & open a SqlConnection, and dispose of it after we are done
        Dim connection As MySqlConnection
        Try
            connection = New MySqlConnection(cadenaConexion)
            connection.Open()

            ' Call the overload that takes a connection in place of the connection string
            Return ExecuteSelect(connection, consulta, commandParameters)
        Finally
            If Not connection Is Nothing Then connection.Dispose()
        End Try
    End Function

    Private Shared Function ExecuteSelect(ByVal connection As MySqlConnection, ByVal consulta As String, ByVal ParamArray commandParameters() As MySqlParameter) As DataSet
        If (connection Is Nothing) Then Throw New ArgumentNullException("connection")

        ' Create a command and prepare it for execution
        Dim cmd As New MySqlCommand
        Dim ds As New DataSet
        Dim dataAdatpter As MySqlDataAdapter
        Dim mustCloseConnection As Boolean = False

        'Agrega los parámetros (si llegan)
        If Not (commandParameters Is Nothing) Then
            AttachParameters(cmd, commandParameters)
        End If

        Try
            ' Create the DataAdapter & DataSet
            cmd.Connection = connection
            cmd.CommandType = CommandType.Text
            cmd.CommandText = consulta
            dataAdatpter = New MySqlDataAdapter(cmd)

            ' Fill the DataSet using default values for DataTable names, etc
            dataAdatpter.Fill(ds)

            ' Detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear()
        Finally
            If (Not dataAdatpter Is Nothing) Then dataAdatpter.Dispose()
        End Try
        If (mustCloseConnection) Then connection.Close()

        ' Return the dataset
        Return ds
    End Function
#End Region


    ''' <summary>
    ''' Agrega lo parámetrod al command que le llega
    ''' </summary>
    ''' <param name="command"></param>
    ''' <param name="commandParameters"></param>
    ''' <remarks></remarks>
    Private Shared Sub AttachParameters(ByVal command As MySqlCommand, ByVal commandParameters() As MySqlParameter)
        If (command Is Nothing) Then Throw New ArgumentNullException("command")
        If (Not commandParameters Is Nothing) Then
            Dim p As MySqlParameter
            For Each p In commandParameters
                If (Not p Is Nothing) Then
                    ' Check for derived output value with no value assigned
                    If (p.Direction = ParameterDirection.InputOutput OrElse p.Direction = ParameterDirection.Input) AndAlso p.Value Is Nothing Then
                        p.Value = DBNull.Value
                    End If
                    command.Parameters.Add(p)
                End If
            Next p
        End If
    End Sub ' AttachParameters

End Class
